import pymysql


class Mysql(object):
    # port必须为init类型
    def __init__(self, ipaddr, user, passwd, port):
        self.host = ipaddr
        self.user = user
        self.passwd = passwd
        self.port = int(port)
        # self.db_name = db_name

    def SelectDatabase(self, sql, db_name):
        try:
            conn = pymysql.connect(
                host=self.host,
                user=self.user,
                passwd=self.passwd,
                port=self.port,
                database=db_name,
                charset='utf8',
                cursorclass=pymysql.cursors.DictCursor
            )
            cur = conn.cursor()
            cur.execute(sql)
            res = cur.fetchall()
            cur.close()
            conn.close()
            return res
        except Exception as e:
            # print(e)
            return False

    def GetAllData(self):
        # 获取所有数据库，排除自带的数据库
        exclude_list = ["sys", "information_schema", "mysql", "performance_schema", "occ", "test"]
        sql_database = "show databases"
        db_res = self.SelectDatabase(sql_database,db_name="mysql")
        if not db_res:
            result = "连接"+self.host+"数据库失败"
            status = "400"
            data = {"status": status, "results": [result]}
            return data
        all_db_data = []
        for dbname_list in db_res:
            db_table_data = {}
            # 清洗数据库名
            db_name = dbname_list['Database']
            if db_name not in exclude_list:
                # 重建获取表名sql
                table_data = {}
                table_sql = "show tables"
                tables_res = self.SelectDatabase(sql=table_sql, db_name=db_name)
                # 清洗表名
                exclude_data = "Tables_in_"+db_name
                for table_list in tables_res:
                    table_name = table_list[exclude_data]
                    # 重建获取字段数据sql
                    desctable_sql = "desc "+table_name
                    desctable_res = self.SelectDatabase(sql=desctable_sql, db_name=db_name)
                    
                    table_data[table_name] = desctable_res
                    
                db_table_data[db_name] = table_data
                all_db_data.append(db_table_data)
                
        if not all_db_data:
            result = "主机"+self.host+"无数据库"
            status = "203"
            data = {"status": status, "results": [result]}
            return data
            
        result = all_db_data
        status = "200"
        data = {"status": status, "results": result}
        return data


if __name__ == '__main__':
    ipaddr = "127.0.0.1"
    user = "root"
    passwd = "123456"
    port = 3306
    # db_name = "mysql"
    
    obj = Mysql(ipaddr, user, passwd, port)
    all_db_list = obj.GetAllData()
    print(all_db_list)
# ConnectMySQL(ipaddr="127.0.0.1", user="root", password="123456", port=3306)
